package com.zhxh.nzpos.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.zhxh.nzpos.db.DBHelper;
import com.zhxh.nzpos.model.Goods;

/**
 * Goods DAO
 * 
 * @author Administrator
 * 
 */
public class GoodsDao {

	DBHelper dbHelper;

	protected static final String TBL_NAME = "goods";
	private final static String LOG_TAG = "GoodsDao";

	public GoodsDao(Context context) {
		dbHelper = new DBHelper(context);
	}

	/**
	 * add new goods
	 * 
	 * @param goods
	 */
	public String save(Goods goods) {

		// goods.setId(RandomStringUtils.randomNumeric(10));
		long time = System.currentTimeMillis();
		goods.setCreated(time);
		goods.setModified(time);
		Log.i(LOG_TAG, "GoodsDao  save " + goods.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"insert into goods(ID,CategoryID,CategoryName,Name,Code,Units1,Units2,Specification,Price1,Price2,Ponits,note,created,modified,type) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
					new Object[] { goods.getId(), goods.getCategoryId(),
							goods.getCategoryName(), goods.getName(),
							goods.getCode(), goods.getUnits1(),
							goods.getUnits2(), goods.getSpecification(),
							goods.getPrice1(), goods.getPrice2(),
							goods.getPonits(), goods.getNote(),
							goods.getCreated(), goods.getModified(),
							goods.getType() });

			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("GoodsDao  save", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
		return goods.getId();
	}

	/**
	 * update a goods
	 * 
	 * @param goods
	 */

	public int update(Goods goods) {
		
		goods.setModified(System.currentTimeMillis());
		Log.i(LOG_TAG, "GoodsDao  update " + goods.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		//db.beginTransaction();// 开始事务
		ContentValues cv = new ContentValues();
		//cv.put("ID", goods.getId());
		cv.put("CategoryID", goods.getCategoryId());
		cv.put("CategoryName", goods.getCategoryName());
		cv.put("Name", goods.getName());
		cv.put("Code", goods.getCode());
		cv.put("Specification", goods.getSpecification());
		
		cv.put("Price1", goods.getPrice1());
		cv.put("Price2", goods.getPrice2());
		cv.put("Units1", goods.getUnits1());
		cv.put("Units2", goods.getUnits2());
		cv.put("modified", goods.getModified());
		int count = db.update(TBL_NAME, cv, "ID="+goods.getId(),null);
		Log.i("###############################=", count+"");
/*		try {
			db.execSQL(
					"update goods set Price1=?, Price2=?, Ponits=?,modified=? where ID=?",
					new Object[] { 
							goods.getPrice1(), goods.getPrice2(),
							goods.getPonits(), 
							goods.getModified(), goods.getId() });

			// db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("GoodsDao  update", e.getMessage());
		}*/
		//db.endTransaction();// 事务结束;
		db.close();
		
		return count;
	}
	
public int updatePrice(Goods goods) {
		
		goods.setModified(System.currentTimeMillis());
		Log.i(LOG_TAG, "GoodsDao  update " + goods.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("Price1", goods.getPrice1());
		cv.put("Price2", goods.getPrice2());
		cv.put("modified", goods.getModified());
		int count = db.update(TBL_NAME, cv, "ID="+goods.getId(),null);
		Log.i("###############################=", count+"");
		db.close();
		
		return count;
	}

	/**
	 * update a goods
	 * 
	 * @param goods
	 */

/*	public void update(Goods goods) {
		
		goods.setModified(System.currentTimeMillis());
		Log.i(LOG_TAG, "GoodsDao  update " + goods.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"update goods set CategoryID=?,CategoryName=?, Name=?, Code=?,Units1=?,Units2=?, Specification=?, Price1=?, Price2=?, Ponits=?, note=?,modified=? where ID=?",
					new Object[] { goods.getCategoryId(),
							goods.getCategoryName(), goods.getName(),
							goods.getCode(), goods.getUnits1(),
							goods.getUnits2(), goods.getSpecification(),
							goods.getPrice1(), goods.getPrice2(),
							goods.getPonits(), goods.getNote(),
							goods.getModified(), goods.getId() });

			 db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("GoodsDao  update", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
	}*/
	/**
	 * get a goods by id or code
	 * 
	 * @param i
	 * @return
	 */
	public Goods find(String id) {
		Log.i(LOG_TAG, "GoodsDao  find " + id);
		Goods goods = null;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,CategoryID,CategoryName,Name,Code,Units1,Units2,Specification,Price1,Price2,Ponits,note,created,modified,type from goods where ID=? or code=?",
						new String[] { id,id });
		if (cursor.moveToNext()) {
			goods = new Goods();

			goods.setId(cursor.getString(cursor.getColumnIndex("ID")));
			goods.setCategoryId(cursor.getString(1));
			goods.setCategoryName(cursor.getString(2));
			goods.setName(cursor.getString(3));
			goods.setCode(cursor.getString(4));
			goods.setUnits1(cursor.getString(5));
			goods.setUnits2(cursor.getString(6));
			goods.setSpecification(cursor.getString(7));
			goods.setPrice1(cursor.getFloat(8));
			goods.setPrice2(cursor.getFloat(9));
			goods.setPonits(cursor.getInt(10));
			goods.setNote(cursor.getString(11));
			goods.setCreated(cursor.getLong(12));
			goods.setModified(cursor.getLong(13));
			goods.setType(cursor.getInt(14));
		}
		cursor.close();
		db.close();
		return goods;
	}
	/**
	 * get a goods by id or code
	 * 
	 * @param i
	 * @return
	 */
	public int findByCategoryId(String id) {
		Log.i(LOG_TAG, "GoodsDao  find " + id);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID from goods where CategoryID=?",
						new String[] { id});
		int count = cursor.getCount();
		cursor.close();
		db.close();
		return count;
	}
	/**
	 * delete a goods by id
	 * 
	 * @param id
	 */
	public void delete(String id) {
		Log.i(LOG_TAG, "GoodsDao  delete " + id);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from goods where ID=?", new Object[] { id });
		db.close();
	}

	/**
	 * delete all
	 * 
	 * @param id
	 */
	public void deleteAll() {
		Log.i(LOG_TAG, "GoodsDao  delete all from  goods: ");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from goods");
		db.close();
	}

	/**
	 * fetch goodss
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public List<Goods> getScrollData(int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "GoodsDao  getScrollData " + pageNumber + "," + pageSize);

		List<Goods> goodss = new ArrayList<Goods>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select  ID,CategoryID,CategoryName,Name,Code,Units1,Units2,Specification,Price1,Price2,Ponits,note,created,modified,type from goods order by modified desc limit ?,?",
						new String[] {
								String.valueOf((pageNumber - 1) * pageSize),
								String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Goods goods = new Goods();
			goods.setId(cursor.getString(cursor.getColumnIndex("ID")));
			goods.setCategoryId(cursor.getString(1));
			goods.setCategoryName(cursor.getString(2));
			goods.setName(cursor.getString(3));
			goods.setCode(cursor.getString(4));
			goods.setUnits1(cursor.getString(5));
			goods.setUnits2(cursor.getString(6));
			goods.setSpecification(cursor.getString(7));
			goods.setPrice1(cursor.getFloat(8));
			goods.setPrice2(cursor.getFloat(9));
			goods.setPonits(cursor.getInt(10));
			goods.setNote(cursor.getString(11));
			goods.setCreated(cursor.getLong(12));
			goods.setModified(cursor.getLong(13));
			goods.setType(cursor.getInt(14));
			goodss.add(goods);
		}
		cursor.close();
		db.close();
		return goodss;
	}

	public int getCount(String cateId, String identifier, long btime, long etime) {
		Log.i(LOG_TAG, "GoodsDao  getScrollData " + cateId + "," + identifier);

		int count = 0;
		String sql = "select  count(ID) from goods where 1=1 ";
		if (identifier.length() != 0) {
			sql += " and ID=" + identifier;
		} else {
			if (cateId != null) {
				sql += " and CategoryID=" + cateId;
			}
			sql += " and created >=" + btime + " and created <=" + etime;
		}

		Log.i("sql= ", sql);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);// pageNumber页码
        if(cursor.moveToNext()){
        	count = cursor.getInt(0);
        }
		
		cursor.close();
		db.close();
		return count;

	}

	/**
	 * fetch goodss
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Goods> getScrollData(String cateId, String identifier,
			long btime, long etime, int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "GoodsDao  getScrollData " + pageNumber + "," + pageSize);

		String sql = "select  ID,CategoryID,CategoryName,Name,Code,Units1,Units2,Specification,Price1,Price2,Ponits,note,created,modified,type from goods where 1=1 ";
		if (identifier != null && identifier.length() != 0) {
			sql += " and ID=" + identifier;
		} else {
			if (cateId != null) {
				sql += " and CategoryID=" + cateId;
			}
			sql += " and created >=" + btime + " and created <=" + etime;
		}

		sql += "  order by modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Goods> goodss = new ArrayList<Goods>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf((pageNumber - 1) * pageSize),
						String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Log.i("aaa=", "11111111111111111111111111");
			Goods goods = new Goods();
			goods.setId(cursor.getString(cursor.getColumnIndex("ID")));
			goods.setCategoryId(cursor.getString(1));
			goods.setCategoryName(cursor.getString(2));
			goods.setName(cursor.getString(3));
			goods.setCode(cursor.getString(4));
			goods.setUnits1(cursor.getString(5));
			goods.setUnits2(cursor.getString(6));
			goods.setSpecification(cursor.getString(7));
			goods.setPrice1(cursor.getFloat(8));
			goods.setPrice2(cursor.getFloat(9));
			goods.setPonits(cursor.getInt(10));
			goods.setNote(cursor.getString(11));
			goods.setCreated(cursor.getLong(12));
			goods.setModified(cursor.getLong(13));
			goods.setType(cursor.getInt(14));
			goodss.add(goods);
		}
		cursor.close();
		db.close();
		return goodss;
	}

	/**
	 * fetch goodss
	 * 
	 * @return
	 */
	public List<Goods> getAll() {

		Log.i(LOG_TAG, "GoodsDao  getAll ");

		List<Goods> goodss = new ArrayList<Goods>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select  ID,CategoryID,CategoryName,Name,Code,Units1,Units2,Specification,Price1,Price2,Ponits,note,created,modified,type from goods  order by modified desc",
						null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Goods goods = new Goods();
			goods.setId(cursor.getString(cursor.getColumnIndex("ID")));
			goods.setCategoryId(cursor.getString(1));
			goods.setCategoryName(cursor.getString(2));
			goods.setName(cursor.getString(3));
			goods.setCode(cursor.getString(4));
			goods.setUnits1(cursor.getString(5));
			goods.setUnits2(cursor.getString(6));
			goods.setSpecification(cursor.getString(7));
			goods.setPrice1(cursor.getFloat(8));
			goods.setPrice2(cursor.getFloat(9));
			goods.setPonits(cursor.getInt(10));
			goods.setNote(cursor.getString(11));
			goods.setCreated(cursor.getLong(12));
			goods.setModified(cursor.getLong(13));
			goods.setType(cursor.getInt(14));
			goodss.add(goods);
		}
		cursor.close();
		db.close();
		return goodss;
	}

	/**
	 * get goods's count
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		long count = DatabaseUtils.queryNumEntries(db, TBL_NAME);
		db.close();
		return count;
	}

	

	public int getCount(String cateId) {
		Log.i(LOG_TAG, "GoodsDao  getScrollData " + cateId);

		int count = 0;
		String sql = "select  count(goods.ID) from goods,category where goods.CategoryID = category.ID and category.pid='"+cateId+"'";
		

		Log.i("sql= ", sql);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);// pageNumber页码
        if(cursor.moveToNext()){
        	count = cursor.getInt(0);
        }
		
		cursor.close();
		db.close();
		return count;

	}

	/**
	 * fetch goodss
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Goods> getScrollData(String cateId, int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "GoodsDao  getScrollData " + pageNumber + "," + pageSize);

		String sql = "select  goods.ID,goods.CategoryID,category.category,goods.Name,goods.Code,goods.Units1,goods.Units2,goods.Specification,goods.Price1,goods.Price2,goods.Ponits,goods.note,goods.created,goods.modified,goods.type from goods,category where goods.CategoryID = category.ID and category.pid='"+cateId+"'";

		sql += "  order by goods.modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Goods> goodss = new ArrayList<Goods>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf(pageNumber),
						String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Log.i("aaa=", "11111111111111111111111111");
			Goods goods = new Goods();
			goods.setId(cursor.getString(0));
			goods.setCategoryId(cursor.getString(1));
			goods.setCategoryName(cursor.getString(2));
			goods.setName(cursor.getString(3));
			goods.setCode(cursor.getString(4));
			goods.setUnits1(cursor.getString(5));
			goods.setUnits2(cursor.getString(6));
			goods.setSpecification(cursor.getString(7));
			goods.setPrice1(cursor.getFloat(8));
			goods.setPrice2(cursor.getFloat(9));
			goods.setPonits(cursor.getInt(10));
			goods.setNote(cursor.getString(11));
			goods.setCreated(cursor.getLong(12));
			goods.setModified(cursor.getLong(13));
			goods.setType(cursor.getInt(14));
			goodss.add(goods);
		}
		cursor.close();
		db.close();
		return goodss;
	}
	
	
	public int getChildCount(String cateId) {
		Log.i(LOG_TAG, "getChildCount " + cateId);

		int count = 0;
		String sql = "select  count(ID) from goods where goods.CategoryID ='"+cateId+"'";
		

		Log.i("sql= ", sql);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);// pageNumber页码
        if(cursor.moveToNext()){
        	count = cursor.getInt(0);
        }
		
		cursor.close();
		db.close();
		return count;

	}

	/**
	 * fetch goodss
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Goods> getChildScrollData(String cateId, int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "GoodsDao  getChildScrollData " + pageNumber + "," + pageSize);

		String sql = "select  goods.ID,goods.CategoryID,category.category,goods.Name,goods.Code,goods.Units1,goods.Units2,goods.Specification,goods.Price1,goods.Price2,goods.Ponits,goods.note,goods.created,goods.modified,goods.type from goods,category where goods.CategoryID = category.ID and goods.CategoryID='"+cateId+"'";

		sql += "  order by goods.modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Goods> goodss = new ArrayList<Goods>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf(pageNumber),
						String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Log.i("aaa=", "11111111111111111111111111");
			Goods goods = new Goods();
			goods.setId(cursor.getString(0));
			goods.setCategoryId(cursor.getString(1));
			goods.setCategoryName(cursor.getString(2));
			goods.setName(cursor.getString(3));
			goods.setCode(cursor.getString(4));
			goods.setUnits1(cursor.getString(5));
			goods.setUnits2(cursor.getString(6));
			goods.setSpecification(cursor.getString(7));
			goods.setPrice1(cursor.getFloat(8));
			goods.setPrice2(cursor.getFloat(9));
			goods.setPonits(cursor.getInt(10));
			goods.setNote(cursor.getString(11));
			goods.setCreated(cursor.getLong(12));
			goods.setModified(cursor.getLong(13));
			goods.setType(cursor.getInt(14));
			goodss.add(goods);
		}
		cursor.close();
		db.close();
		return goodss;
	}
	
}
